﻿Imports System.Data
Imports System.Data.SqlClient
Imports BE = BusinessEntities

Public Class UsuarioDAO
    Dim conexion As SqlConnection
    ' Dim cmd As New SqlDataAdapter
    Dim cn As New Conexion
    Dim str_error As String
    Dim da As SqlDataAdapter
    Dim bool_resultado As Boolean
    Dim cmd As SqlCommand

    Public Function AutentificarUsuario(ByVal id_usu As String, ByVal pas_usu As String) As DataSet
        Dim ds As New DataSet
        Dim str_error As String
        Try
            conexion = cn.Conectar
            da = New SqlDataAdapter("SP_AUTENTIFICAR", conexion)
            da.SelectCommand.CommandType = CommandType.StoredProcedure

            da.SelectCommand.Parameters.Add(New SqlParameter("@CODIGO", SqlDbType.VarChar, 50))
            da.SelectCommand.Parameters("@CODIGO").Direction = ParameterDirection.Input
            da.SelectCommand.Parameters("@CODIGO").Value = id_usu

            da.SelectCommand.Parameters.Add(New SqlParameter("@PASSWORD", SqlDbType.VarChar, 50))
            da.SelectCommand.Parameters("@PASSWORD").Direction = ParameterDirection.Input
            da.SelectCommand.Parameters("@PASSWORD").Value = pas_usu

            da.Fill(ds, "UsuarioValidado")
        Catch ex As Exception
            str_error = ex.Message
        Finally
            conexion.Dispose()
            ds.Dispose()
            da.Dispose()
        End Try

        Return ds

    End Function

    Public Function BuscarUsuario(ByVal objeto As BE.UsuarioDTO) As DataSet
        Dim ds As New DataSet
        Dim str_error As String
        Try
            conexion = cn.Conectar
            da = New SqlDataAdapter("SP_BUSCARUSUARIO", conexion)
            da.SelectCommand.CommandType = CommandType.StoredProcedure

            da.SelectCommand.Parameters.Add(New SqlParameter("@CODIGO", SqlDbType.Int))
            da.SelectCommand.Parameters("@CODIGO").Direction = ParameterDirection.Input
            da.SelectCommand.Parameters("@CODIGO").Value = objeto.CodigoUsuario

            da.Fill(ds, "BuscarUsuario")
        Catch ex As Exception
            str_error = ex.Message
        Finally
            conexion.Dispose()
            ds.Dispose()
            da.Dispose()
        End Try

        Return ds

    End Function

    Public Function Listarusuxproy() As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            da = New SqlDataAdapter("SP_USUARIOS_PROY", conexion)
            'Indicamos que el comando de base de datos es un stored procedure
            da.SelectCommand.CommandType = CommandType.StoredProcedure

            'Llenamos el dataset
            da.Fill(ds, "USUARIO_CONTRASEÑA")


        Catch ex As Exception
            str_error = ex.Message
        Finally
            'Liberamos recursos
            conexion.Dispose()
            da.Dispose()
            da.Dispose()
        End Try

        Return ds

    End Function





    Public Function usuariosXproyecto(ByVal objeto As BE.UsuarioDTO) As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            da = New SqlDataAdapter("SP_lISTAPROYECT_US", conexion)
            'Indicamos que el comando de base de datos es un stored procedure
            da.SelectCommand.CommandType = CommandType.StoredProcedure

            'Definimos el parámetro
            da.SelectCommand.Parameters.Add _
            (New SqlParameter("@vc_cod_usu", SqlDbType.Int))

            da.SelectCommand.Parameters("@vc_cod_usu").Direction = ParameterDirection.Input
            da.SelectCommand.Parameters("@vc_cod_usu").Value = objeto.CodigoUsuario

            'Llenamos el dataset
            da.Fill(ds, "Ventas")

        Catch ex As Exception
            str_error = ex.Message
        Finally
            'Liberamos recursos
            conexion.Dispose()
            ds.Dispose()
            da.Dispose()
        End Try

        Return ds

    End Function


    Public Function detallerendicion(ByVal objeto As BE.UsuarioDTO) As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            da = New SqlDataAdapter("PR_DETALLE_DE_REQUISIONES", conexion)
            'Indicamos que el comando de base de datos es un stored procedure
            da.SelectCommand.CommandType = CommandType.StoredProcedure

            'Definimos el parámetro
            da.SelectCommand.Parameters.Add _
            (New SqlParameter("@vc_cod_usu", SqlDbType.Int))

            da.SelectCommand.Parameters("@vc_cod_usu").Direction = ParameterDirection.Input
            da.SelectCommand.Parameters("@vc_cod_usu").Value = objeto.CodigoUsuario

            'Llenamos el dataset
            da.Fill(ds, "rendicion")

        Catch ex As Exception
            str_error = ex.Message
        Finally
            'Liberamos recursos
            conexion.Dispose()
            ds.Dispose()
            da.Dispose()
        End Try

        Return ds

    End Function

    Public Function ListarUsuarios() As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            da = New SqlDataAdapter("SP_LISTARUSUARIOS", conexion)
            'Indicamos que el comando de base de datos es un stored procedure
            da.SelectCommand.CommandType = CommandType.StoredProcedure

            'Llenamos el dataset
            da.Fill(ds, "proyectos")

        Catch ex As Exception
            str_error = ex.Message
        Finally
            'Liberamos recursos
            conexion.Dispose()
            ds.Dispose()
            da.Dispose()
        End Try

        Return ds

    End Function
    Public Function Insertaringeniero(ByVal objeto As BE.UsuarioDTO) As Boolean

        'Instanciamos la clase conexion
        Dim cn As New Conexion
        Try
            conexion = cn.Conectar

            cmd = New SqlCommand("pr_insertar_USUARIO", conexion)
            'da = New SqlDataAdapter("INSERT INTO TB_USUARIOS (NOM_USU,APE_USU,APEM_USU,ID_USU,PAS_USU,PROFESION,CORREO,FECHA,DIRECCION,COD_ARE,COD_CAR) VALUES(NULL,?,?,?,?,?,?,?,?,?,?,?)", conexion)

            cmd.CommandType = CommandType.StoredProcedure

            ''Definimos los parámetros de entrada
            cmd.Parameters.Add(New SqlParameter("@vc_NOMBRE", SqlDbType.VarChar, 50))
            cmd.Parameters("@vc_NOMBRE").Direction = ParameterDirection.Input
            cmd.Parameters("@vc_NOMBRE").Value = objeto.Nombre


            cmd.Parameters.Add(New SqlParameter("@vc_APELLIDOPAT", SqlDbType.VarChar, 50))
            cmd.Parameters("@vc_APELLIDOPAT").Direction = ParameterDirection.Input
            cmd.Parameters("@vc_APELLIDOPAT").Value = objeto.ApellidoUsuario

            cmd.Parameters.Add(New SqlParameter("@vc_APELLIDOMAT", SqlDbType.VarChar, 50))
            cmd.Parameters("@vc_APELLIDOMAT").Direction = ParameterDirection.Input
            cmd.Parameters("@vc_APELLIDOMAT").Value = objeto.ApellidoUsuariomat

            cmd.Parameters.Add(New SqlParameter("@vc_USUARIO", SqlDbType.VarChar, 50))
            cmd.Parameters("@vc_USUARIO").Direction = ParameterDirection.Input
            cmd.Parameters("@vc_USUARIO").Value = objeto.IdUsuario

            cmd.Parameters.Add(New SqlParameter("@vc_PASSWORD", SqlDbType.VarChar, 50))
            cmd.Parameters("@vc_PASSWORD").Direction = ParameterDirection.Input
            cmd.Parameters("@vc_PASSWORD").Value = objeto.PasswordUsuario


            cmd.Parameters.Add(New SqlParameter("@vc_PROFESION", SqlDbType.VarChar, 50))
            cmd.Parameters("@vc_PROFESION").Direction = ParameterDirection.Input
            cmd.Parameters("@vc_PROFESION").Value = objeto.profesiones

            cmd.Parameters.Add(New SqlParameter("@vc_EMAIL", SqlDbType.VarChar, 50))
            cmd.Parameters("@vc_EMAIL").Direction = ParameterDirection.Input
            cmd.Parameters("@vc_EMAIL").Value = objeto.emails



            cmd.Parameters.Add(New SqlParameter("@vc_DIRECCION", SqlDbType.VarChar, 50))
            cmd.Parameters("@vc_DIRECCION").Direction = ParameterDirection.Input
            cmd.Parameters("@vc_DIRECCION").Value = objeto.dir


            cmd.Parameters.Add(New SqlParameter("@COD_ARE", SqlDbType.Int))
            cmd.Parameters("@COD_ARE").Direction = ParameterDirection.Input
            cmd.Parameters("@COD_ARE").Value = objeto.CodigoArea


            cmd.Parameters.Add(New SqlParameter("@COD_CAR", SqlDbType.Int))
            cmd.Parameters("@COD_CAR").Direction = ParameterDirection.Input
            cmd.Parameters("@COD_CAR").Value = objeto.CodigoCargo





            conexion.Open()

            cmd.ExecuteNonQuery()
            bool_resultado = True

        Catch ex As Exception
            bool_resultado = False
            str_error = ex.Message
        Finally

            If conexion.State = ConnectionState.Open Then conexion.Close()
            conexion.Dispose()
            cmd.Dispose()
            cn = Nothing

        End Try

        Return bool_resultado

    End Function
    Public Function getArea() As DataSet
        Dim cn As New Conexion
        Dim ds As New DataSet
        Dim da As SqlDataAdapter

        Try
            conexion = cn.Conectar
            da = New SqlDataAdapter("Select * from tb_areas ", conexion)
            'Indicamos que el comando de base de datos es un stored procedure
            ' da.SelectCommand.CommandType = CommandType.StoredProcedure

            'Llenamos el dataset
            da.Fill(ds, "proyectos")



        Catch ex As Exception
            bool_resultado = False
            str_error = ex.Message
        Finally

            If conexion.State = ConnectionState.Open Then conexion.Close()
            conexion.Dispose()
            'cmd.Dispose()
            cn = Nothing
        End Try

        Return ds

    End Function

    Public Function getCargo() As DataSet
        Dim cn As New Conexion
        Dim ds As New DataSet

        Try

            conexion = cn.Conectar
            da = New SqlDataAdapter("Select * from tb_cargos ", conexion)
            'Indicamos que el comando de base de datos es un stored procedure
            ' da.SelectCommand.CommandType = CommandType.StoredProcedure

            'Llenamos el dataset
            da.Fill(ds, "cargos")

        Catch ex As Exception
            bool_resultado = False
            str_error = ex.Message
        Finally

            If conexion.State = ConnectionState.Open Then conexion.Close()
            conexion.Dispose()
            ' cmd.Dispose()
            cn = Nothing
        End Try

        Return ds

    End Function

    Public Function EliminarUsuario(ByVal objeto As BE.UsuarioDTO) As Boolean
        'Instanciamos la clase conexion
        Dim cn As New Conexion
        Try

            conexion = cn.Conectar

            cmd = New SqlCommand("pr_eliminarUsuario", conexion)

            cmd.CommandType = CommandType.StoredProcedure

            'Definimos el parámetro de entrada
            cmd.Parameters.Add(New SqlParameter("@vc_codigo", SqlDbType.Int))
            cmd.Parameters("@vc_codigo").Direction = ParameterDirection.Input
            cmd.Parameters("@vc_codigo").Value = objeto.CodigoUsuario

            conexion.Open()

            cmd.ExecuteNonQuery()
            bool_resultado = True


        Catch ex As Exception

            bool_resultado = False
            str_error = ex.Message

        Finally

            If conexion.State = ConnectionState.Open Then conexion.Close()
            conexion.Dispose()
            cmd.Dispose()
            cn = Nothing

        End Try

        Return bool_resultado

    End Function

    Public Function DatosUsuario(ByVal objeto As BE.UsuarioDTO) As DataSet

        Dim ds As New DataSet

        Try

            conexion = cn.Conectar
            da = New SqlDataAdapter("pr_datos_usuario", conexion)
            da.SelectCommand.CommandType = CommandType.StoredProcedure

            da.SelectCommand.Parameters.Add _
            (New SqlParameter("@vc_codigo", SqlDbType.Int))

            da.SelectCommand.Parameters("@vc_codigo").Direction = ParameterDirection.Input

            da.SelectCommand.Parameters("@vc_codigo").Value = objeto.CodigoUsuario
            'Llenamos el dataset
            da.Fill(ds, "DatosProyecto")

        Catch ex As Exception
            str_error = ex.Message
        Finally
            'Destruimos los objetos
            conexion.Dispose()
            da.Dispose()
            ds.Dispose()
        End Try

        Return ds

    End Function

    Public Function ActualizarUsuario(ByVal objeto As BE.UsuarioDTO) As Boolean

        'Instanciamos la clase conexion
        Dim cn As New Conexion
        Try
            conexion = cn.Conectar

            cmd = New SqlCommand("pr_actualizar_Usuario", conexion)

            cmd.CommandType = CommandType.StoredProcedure

            'Definimos los parámetros de entrada

            cmd.Parameters.Add(New SqlParameter("@vc_CODIGO", SqlDbType.Int))
            cmd.Parameters("@vc_CODIGO").Direction = ParameterDirection.Input
            cmd.Parameters("@vc_CODIGO").Value = objeto.CodigoUsuario

            cmd.Parameters.Add(New SqlParameter("@vc_NOMBRE", SqlDbType.VarChar, 50))
            cmd.Parameters("@vc_NOMBRE").Direction = ParameterDirection.Input
            cmd.Parameters("@vc_NOMBRE").Value = objeto.Nombre


            cmd.Parameters.Add(New SqlParameter("@vc_APELLIDOPAT", SqlDbType.VarChar, 50))
            cmd.Parameters("@vc_APELLIDOPAT").Direction = ParameterDirection.Input
            cmd.Parameters("@vc_APELLIDOPAT").Value = objeto.ApellidoUsuario

            cmd.Parameters.Add(New SqlParameter("@vc_APELLIDOMAT", SqlDbType.VarChar, 50))
            cmd.Parameters("@vc_APELLIDOMAT").Direction = ParameterDirection.Input
            cmd.Parameters("@vc_APELLIDOMAT").Value = objeto.ApellidoUsuariomat

            cmd.Parameters.Add(New SqlParameter("@vc_USUARIO", SqlDbType.VarChar, 50))
            cmd.Parameters("@vc_USUARIO").Direction = ParameterDirection.Input
            cmd.Parameters("@vc_USUARIO").Value = objeto.IdUsuario

            cmd.Parameters.Add(New SqlParameter("@vc_PASSWORD", SqlDbType.VarChar, 50))
            cmd.Parameters("@vc_PASSWORD").Direction = ParameterDirection.Input
            cmd.Parameters("@vc_PASSWORD").Value = objeto.PasswordUsuario


            cmd.Parameters.Add(New SqlParameter("@vc_PROFESION", SqlDbType.VarChar, 50))
            cmd.Parameters("@vc_PROFESION").Direction = ParameterDirection.Input
            cmd.Parameters("@vc_PROFESION").Value = objeto.profesiones

            cmd.Parameters.Add(New SqlParameter("@vc_EMAIL", SqlDbType.VarChar, 50))
            cmd.Parameters("@vc_EMAIL").Direction = ParameterDirection.Input
            cmd.Parameters("@vc_EMAIL").Value = objeto.emails



            cmd.Parameters.Add(New SqlParameter("@vc_DIRECCION", SqlDbType.VarChar, 50))
            cmd.Parameters("@vc_DIRECCION").Direction = ParameterDirection.Input
            cmd.Parameters("@vc_DIRECCION").Value = objeto.dir


            cmd.Parameters.Add(New SqlParameter("@COD_ARE", SqlDbType.Int))
            cmd.Parameters("@COD_ARE").Direction = ParameterDirection.Input
            cmd.Parameters("@COD_ARE").Value = objeto.CodigoArea


            cmd.Parameters.Add(New SqlParameter("@COD_CAR", SqlDbType.Int))
            cmd.Parameters("@COD_CAR").Direction = ParameterDirection.Input
            cmd.Parameters("@COD_CAR").Value = objeto.CodigoCargo


            conexion.Open()

            cmd.ExecuteNonQuery()

            bool_resultado = True

        Catch ex As Exception
            bool_resultado = False
            str_error = ex.Message
        Finally

            If conexion.State = ConnectionState.Open Then conexion.Close()
            conexion.Dispose()
            cmd.Dispose()
            cn = Nothing

        End Try

        Return bool_resultado

    End Function

    


    Public Function CapturaError() As String
        Return str_error
    End Function
End Class
